﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using Entities;
namespace Dao
{
    /// <summary>
    /// Provides a way of query with PersonalMessage from a SQL Server Database
    /// </summary>
    public class PersonalMessageData
    {
        #region Variables private
        private IList<PersonalMessage> _listPersonalMessage;
        private PersonalMessage _personalMessage;
        #endregion

        #region Methods public
        /// <summary>
        /// Get list information personal message
        /// </summary>
        /// <returns>List personal message</returns>
        public IList<PersonalMessage> GetAll()
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            _listPersonalMessage = new List<PersonalMessage>();
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_PersonalMessage_GetAll", null);
                while (reader.Read())
                {
                    _personalMessage = new PersonalMessage();
                    if (!reader.IsDBNull(0))
                        _personalMessage.FromId = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _personalMessage.ToId = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _personalMessage.Subject = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _personalMessage.SentDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _personalMessage.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _personalMessage.IsRead = reader.GetBoolean(5);
                    _listPersonalMessage.Add(_personalMessage);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listPersonalMessage;
        }
        /// <summary>
        /// Get details of a personal message by Id of sender
        /// </summary>
        /// <param name="fromId">Id of sender</param>
        /// <returns>Details message</returns>
        public PersonalMessage GetByFromId(int fromId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@fromId", SqlDbType.Int, 2) };
                parm[0].Value = fromId;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_PersonalMessage_GetByFromId", parm);
                while (reader.Read())
                {
                    _personalMessage = new PersonalMessage();
                    if (!reader.IsDBNull(0))
                        _personalMessage.FromId = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _personalMessage.ToId = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _personalMessage.Subject = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _personalMessage.SentDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _personalMessage.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _personalMessage.IsRead = reader.GetBoolean(5);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _personalMessage;
        }
        /// <summary>
        /// Get details of a personal message by send date time
        /// </summary>
        /// <param name="sendDate">Send date time</param>
        /// <returns>Details message</returns>
        public PersonalMessage GetByDate(DateTime sendDate)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@sendDate", SqlDbType.DateTime) };
                parm[0].Value = sendDate;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_PersonalMessage_GetByDate", parm);
                while (reader.Read())
                {
                    _personalMessage = new PersonalMessage();
                    if (!reader.IsDBNull(0))
                        _personalMessage.FromId = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _personalMessage.ToId = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _personalMessage.Subject = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _personalMessage.SentDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _personalMessage.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _personalMessage.IsRead = reader.GetBoolean(5);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _personalMessage;
        }
        /// <summary>
        /// Get details of personal message is unread or read
        /// </summary>
        /// <param name="isRead">False get unread, true get read</param>
        /// <returns>Details message unread or read</returns>
        public PersonalMessage GetByIsRead(bool isRead)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@isRead", SqlDbType.Bit) };
                parm[0].Value = isRead;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_PersonalMessage_GetByIsRead", parm);
                while (reader.Read())
                {
                    _personalMessage = new PersonalMessage();
                    if (!reader.IsDBNull(0))
                        _personalMessage.FromId = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _personalMessage.ToId = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _personalMessage.Subject = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _personalMessage.SentDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _personalMessage.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _personalMessage.IsRead = reader.GetBoolean(5);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _personalMessage;
        }
        /// <summary>
        /// Insert or Update a personal message
        /// If PersonalMessageId = 1 then Insert a personal message
        /// If PersonalMessageId != 1 then Update a personal message
        /// </summary>
        /// <param name="personalMessage">colection properties of class PersonalMessage</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool InsertOrUpdate(PersonalMessage personalMessage)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter("@FromId", SqlDbType.Int, 2),
                        new SqlParameter("@ToId", SqlDbType.Int, 2),
                        new SqlParameter("@Subject", SqlDbType.NVarChar, 150),
                        new SqlParameter("@SentDate", SqlDbType.DateTime),
                        new SqlParameter("@Content", SqlDbType.NText),
                        new SqlParameter("@IsRead", SqlDbType.Bit) };

                parms[0].Value = personalMessage.FromId;
                parms[1].Value = personalMessage.ToId;
                parms[2].Value = personalMessage.Subject;
                parms[3].Value = personalMessage.SentDate;
                parms[4].Value = personalMessage.Content;
                parms[5].Value = personalMessage.IsRead;

                if (personalMessage.PersonalMessageId == 1)
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_PersonalMessage_Update", parms);
                else
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_PersonalMessage_Insert", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// Delete a personal message
        /// </summary>
        /// <param name="PersonalMessageId">Id of personal message want to delete </param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool DeleteById(int PersonalMessageId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@PersonalMessageId", SqlDbType.Int, 2) };
                parm[0].Value = PersonalMessageId;
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_PersonalMessage_DeleteyId", parm);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        #endregion
    }


}
